/****** Object:  StoredProcedure [dbo].[AuthZ_spInsertUserToRole]    Script Date: 06/24/2009 23:48:02 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuthZ_spInsertUserToRole]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AuthZ_spInsertUserToRole]
GO

/****** Object:  StoredProcedure [dbo].[AuthZ_spInsertUserToRole]    Script Date: 06/24/2009 23:48:02 ******/
SET ANSI_NULLS ON
GO

-- =============================================
-- Author:		<revans>
-- Create date: <5-13-2009>
-- Description:	<Inserts the AuthZUser to Role Mapping.>
-- Param:
-- =============================================
CREATE PROCEDURE [dbo].[AuthZ_spInsertUserToRole]

	@role as nvarchar(50),
	@displayName as nvarchar(100),
	@personnelNumber as int,
	@sAMAccountName as nvarchar(50),
	@alias nvarchar(50)

AS
BEGIN

	DECLARE @date AS datetime
	SET @date = GETUTCDATE()

	BEGIN TRY
		SET NOCOUNT ON;
		
		--First determine if the role is valid.  If it's not valid, don't
		--want to create a new user record and leave it not mapped.
		IF (SELECT COUNT(*)
			FROM [dbo].[Role]
			WHERE RoleName = @role) > 0
		BEGIN
			
			--Update the User record with most recent information from AD.
			UPDATE [dbo].[AuthZUser]
			SET DisplayName = @displayName
			  , sAMAccountName = @sAMAccountName
			  , UpdatedBy = @alias
			  , UpdatedDate = @date
			WHERE PersonnelNumber = @personnelNumber
			
			--If the user record doesn't exist yet.
			IF @@ROWCOUNT = 0
			BEGIN
				INSERT INTO [dbo].[AuthZUser]
				VALUES (@personnelNumber
					  , @displayName
					  , @sAMAccountName
					  , @alias
					  , @date
					  , NULL
					  , NULL)
			END
		  
			--Insert a mapping of the user to the role.
			--But, only if the mapping doesn't already exist and the role exists
			--and the User exists.
			INSERT INTO [dbo].[AuthZUserRoleMapping]
			SELECT au.AuthZUserId
				 , r.RoleID
				 , @alias
				 , @date
				 , NULL
				 , NULL
			FROM [dbo].[Role] AS r
			JOIN [dbo].[AuthZUser] AS au
			ON   au.PersonnelNumber = @personnelNumber
			AND  r.RoleName = @role
			AND  NOT EXISTS
			(SELECT *
			 FROM [dbo].[AuthZUserRoleMapping] AS arm
			 WHERE r.RoleID = arm.RoleID
			 AND   arm.AuthZUserId = au.AuthZUserId)
		END

	END TRY
BEGIN CATCH
	DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();
           
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH
END

GO


